package com.hj545.pl;

import com.hj545.pl.bean.PropertieVO;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class DaoUtil {

	/* slf4j + log4j2 logger */
	private final static Logger logger = LoggerFactory.getLogger(DaoUtil.class);

	private DaoUtil() {}

	private final static String SELECT_DEFALUT = "SELECT  P.ID projectId, GT.ID groupId, D.ID id,\n" +
			"\tP.PROJECT_NAME projectName, GT.GROUP_NAME groupName, D.KEY_TITLE keyTitle,\n" +
			"\tCONCAT(P.PROJECT_NAME, '_', GT.GROUP_NAME, '_', D.KEY_TITLE) AS concatName,\n" +
			"\tD.KEY_NAME keyName, D.IS_ENCRYPTED isEncrypted, D.REMARK remark,\n" +
			"\tD.KEY_VALUE keyValue\n" +
			"FROM TB_CONF_DEFAULT D\n" +
			"LEFT JOIN TB_CONF_PROJECT P ON D.PROJECT_ID = P.ID\n" +
			"LEFT JOIN TB_CONF_GROUP_TYPE GT ON GT.ID = D.GROUP_ID\n" +
			"WHERE P.PROJECT_NAME = '$projectName$' ORDER BY D.GROUP_ID";

	private final static String SELECT_EVN = "SELECT  E.ID pcId, P.ID projectId, GE.ID evnId, GT.ID groupId, D.ID id,\n" +
			"\tP.PROJECT_NAME projectName, GE.EVN_NAME evnName, GT.GROUP_NAME groupName, D.KEY_TITLE keyTitle,\n" +
			"\tCONCAT(P.PROJECT_NAME, '_', GE.EVN_NAME, '_', GT.GROUP_NAME, '_', D.KEY_TITLE) AS concatName,\n" +
			"\tD.KEY_NAME keyName, D.IS_ENCRYPTED isEncrypted, D.REMARK remark,\n" +
			"\tD.KEY_VALUE defKeyValue, E.KEY_VALUE keyValue\n" +
			"FROM TB_CONF_EVN E\n" +
			"LEFT JOIN TB_CONF_PROJECT P ON E.PROJECT_ID = P.ID\n" +
			"LEFT JOIN TB_CONF_GROUP_EVN GE ON GE.ID = E.EVN_ID\n" +
			"LEFT JOIN TB_CONF_GROUP_TYPE GT ON GT.ID = E.GROUP_ID\n" +
			"LEFT JOIN TB_CONF_DEFAULT D ON D.ID = E.DEFAULT_ID\n" +
			"WHERE P.PROJECT_NAME = '$projectName$' \n" +
			"\tAND GE.EVN_NAME = '$evnName$' ORDER BY D.GROUP_ID";

	private final static String SELECT_PC = "SELECT E.ID pcId, P.ID projectId, GE.ID evnId, GT.ID groupId, D.ID id,\n" +
			"\tP.PROJECT_NAME projectName, GE.EVN_NAME evnName, GT.GROUP_NAME groupName, D.KEY_TITLE keyTitle,\n" +
			"\tCONCAT(P.PROJECT_NAME, '_', GE.EVN_NAME, '_', GT.GROUP_NAME, '_', D.KEY_TITLE) AS concatName,\n" +
			"\tD.KEY_NAME keyName, D.IS_ENCRYPTED isEncrypted, D.REMARK remark, E.PC_NAME pcName,\n" +
			"\tD.KEY_VALUE defKeyValue, E.KEY_VALUE keyValue\n" +
			"FROM TB_CONF_PC E\n" +
			"LEFT JOIN TB_CONF_PROJECT P ON E.PROJECT_ID = P.ID\n" +
			"LEFT JOIN TB_CONF_GROUP_EVN GE ON GE.ID = E.EVN_ID\n" +
			"LEFT JOIN TB_CONF_GROUP_TYPE GT ON GT.ID = E.GROUP_ID\n" +
			"LEFT JOIN TB_CONF_DEFAULT D ON D.ID = E.DEFAULT_ID\n" +
			"WHERE P.PROJECT_NAME = '$projectName$' \n" +
			"\tAND GE.EVN_NAME = '$evnName$' \n" +
			"\tAND E.PC_NAME = '$pcName$' ORDER BY D.GROUP_ID";

	private final static String SELECT_ALL_EVN = "SELECT  \n" +
			"\tDISTINCT GE.EVN_NAME evnName, P.PROJECT_NAME projectName\n" +
			"FROM TB_CONF_EVN E\n" +
			"LEFT JOIN TB_CONF_PROJECT P ON E.PROJECT_ID = P.ID\n" +
			"LEFT JOIN TB_CONF_GROUP_EVN GE ON GE.ID = E.EVN_ID\n" +
			"LEFT JOIN TB_CONF_GROUP_TYPE GT ON GT.ID = E.GROUP_ID\n" +
			"LEFT JOIN TB_CONF_DEFAULT D ON D.ID = E.DEFAULT_ID\n" +
			"WHERE P.PROJECT_NAME = '$projectName$' ";



	public static Connection getConn(String url, String username, String password) {
		//声明Connection对象
		Connection con = null;
		//驱动程序名
		String driver = "com.mysql.jdbc.Driver";
		try {
			//加载驱动程序
			Class.forName(driver);
			con = DriverManager.getConnection(url, username, password);
		} catch (Exception e) {
			logger.error("", e);
		}
		return con;
	}


	public static Connection getConn(String ip, String port, String dbname, String username, String password) {
		String url = "jdbc:mysql://" + ip + ":" + port + "/" + dbname + "?useUnicode=true&characterEncoding=UTF-8";
		return getConn(url, username, password);
	}

	public static List<PropertieVO> selectDefault(Connection conn, String projectName) {
		return selectProperties(conn, SELECT_DEFALUT, projectName, null, null);
	}

	public static List<PropertieVO> selectEvn(Connection conn, String projectName, String evnName) {
		return selectProperties(conn, SELECT_EVN, projectName, evnName, null);
	}

	public static List<PropertieVO> selectPc(Connection conn, String projectName, String evnName, String pcName) {
		if (StringUtils.isBlank(pcName)) {
			return new ArrayList<>();
		}
		return selectProperties(conn, SELECT_PC, projectName, evnName, pcName);
	}

	public static List<Map<String, Object>> selectAllEvn(Connection conn, String projectName) {
		QueryRunner qRunner = new QueryRunner();
		List<Map<String, Object>> list = null;
		try {
			String sql = SELECT_ALL_EVN.replace("$projectName$", projectName);
			list = qRunner.query(conn, sql, new MapListHandler());
		} catch (SQLException e) {
			logger.error("", e);
		}
		return list;
	}

	private static List<PropertieVO> selectProperties(Connection conn, String sql, String projectName, String evnName, String pcName) {
		QueryRunner qRunner = new QueryRunner();
		List<PropertieVO> list = null;
		try {
			sql = sql.replace("$projectName$", projectName)
					.replace("$evnName$", evnName == null ? "" : evnName)
					.replace("$pcName$", pcName == null ? "" : pcName);
			list = (List<PropertieVO>) qRunner.query(conn, sql, new BeanListHandler(PropertieVO.class));
		} catch (SQLException e) {
			logger.error("", e);
		}
		return list;
	}
}
